package com.white.algebra.dao.impl;

import com.white.algebra.dao.UserDao;
import com.white.algebra.domain.Syslog;
import com.white.algebra.domain.UserInfo;
import com.white.algebra.utils.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public UserInfo login(String username, String password) throws Exception {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "select * from users where username=? and password=?";
        Object[] params = { username,password};
        UserInfo userInfo= (UserInfo) qr.query(sql,params, new BeanHandler(UserInfo.class));
        return userInfo;

    }

    @Override
    public void register(String username, String password,String email) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "INSERT INTO users (`username`, `password`,`email`) VALUES (?, ?, ?)";
        Object[] params = {username,password,email};
        qr.update(sql, params);
    }

    @Override
    public List<UserInfo> findAll(int startRecord, int size, String fuzzyName) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<UserInfo> userList=null;
        if(fuzzyName!=null&&fuzzyName!=""){
            String sql = "select * from users where username like concat('%',?,'%') limit ?,?";
            Object[] params = { fuzzyName,startRecord,size};
            userList= (List<UserInfo>) qr.query(sql,params, new BeanListHandler(UserInfo.class));
        }else {
            String sql = "select * from users limit ?,?";
            Object[] params = {startRecord,size};
            userList= (List<UserInfo>) qr.query(sql,params, new BeanListHandler(UserInfo.class));
        }
        return userList;
    }

    @Override
    public UserInfo findById(String id) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        UserInfo user=null;
        String sql = "select * from users where id = ?";
        Object[] params = {id};
        user= (UserInfo) qr.query(sql,params, new BeanHandler(UserInfo.class));
        return user;
    }

    @Override
    public void save(UserInfo userInfo) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "insert into users(username,email,password,phoneNum,status) values (?, ?,?,?,?)";
        Object[] params = {userInfo.getUsername(),userInfo.getEmail(),userInfo.getPassword(),userInfo.getPhoneNum(),userInfo.getStatus()};
        qr.update(sql, params);
    }

    @Override
    public int getLogNum(String fuzzyName) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        int userNum=0;
        if(fuzzyName!=null&& !fuzzyName.equals("")){
            String sql = "select count(*) from users where username like concat('%',?,'%')";
            Object[] params = { fuzzyName};
            userNum= ((Long) qr.query(sql,params, new ScalarHandler(1))).intValue();
        }else {
            String sql = "select count(*) from users";
            userNum= ((Long) qr.query(sql, new ScalarHandler(1))).intValue();
        }
        return userNum;
    }

    @Override
    public void relatedRole(String userId, String roleId) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "insert into users_role(userId,roleId) values(?,?)";
        Object[] params = {userId,roleId};
        qr.update(sql, params);
    }

    @Override
    public void deleteRoleById(String id, String userId) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "delete from users_role where roleId=? and userId=?";
        Object[] params = {id,userId};
        qr.update(sql, params);
    }

    public static void main(String[] args) throws Exception {
        UserDao userDao=new UserDaoImpl();
        UserInfo userInfo=userDao.login("admin","$2a$10$WOkSm4.XeroznuNnb8iY3O8IHbg85NwMMrl2OJPo8V0ZH0VX6/IDy");
        System.out.println(userInfo);
    }
}
